Release 10.1A: OpenEdge Data Management:
Database Administration

Analyzing index use

Use the PROUTIL IDXANALYS qualifier to get information about index blocks and usage.

To execute the IDXANALYS qualifier, enter the following command:

proutil db-name -C idxanalys

db-name

Specifies the name of the database.

The IDXANALYS qualifier provides:

The number of fields and levels in each index.
The size of each index, in blocks and in bytes.
The percent utilization within the index (that is, the degree of disk space efficiency).
A factor value that indicates whether to rebuild each index.
A summary of indexes for the current database and the percentage of total index space used by each index.
Note: You can run PROUTIL with the IDXANALYS qualifier while the database is in use; however, PROUTIL generates only approximate information.

The most important field in the IDXANALYS display is the % Util field. This field shows the degree of consolidation of each index. If an index is several hundred blocks and your application most frequently retrieves data, an index utilization of 85 percent or higher is optimal. There are two ways to increase an index’s utilization rate:

Compress the index with the database online or offline with the PROUTIL IDXCOMPACT utility.
Rebuild and compress the index offline with the PROUTIL IDXBUILD utility.

The Levels field shows the number of reads PROUTIL performs in each index per entry. The Blocks and Bytes fields show you the size of each index. The Factor field is based on the utilization and size of the index; it is an indicator of when you should rebuild indexes. Table 13–5 provides a description of the different ranges of values for the Factor field. When you use the Factor field to decide whether to rebuild an index, consider the context of how the particular index is used. For example, if an index is highly active, with continuous insertions and deletions, its utilization rate varies greatly, and a rebuild is inadvisable. However, a static index with a high factor value benefits from a rebuild.

Table 13–5: Factor values 

Factor range	Description
1 to 2	The index is well-utilized and balanced. You do not have to rebuild it.
2 to 2.5	The index is less than 50 percent utilized and/or the index is unbalanced. You should consider a rebuild.
2.5 to 3	The index is less than 25 percent utilized and/or the index is very unbalanced. You should rebuild this index.